/*
 * AssociativeTable.java - SQL operations with the table 'acon'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'acon' in the database 'context'.
 * See Ontology.WDAssoc.
 */
public class AssociativeTable extends RelationTable {
    
    /** First class */
    public ClassTable class1;
    
    /** Second class */
    public ClassTable class2;
    
    /** Creates a new instance of AssociativeTable */
    public AssociativeTable() {
    }
    
    
    /** Inserts element to the table 'acon' in MySQL database.
     * 
     * SQL example:
     * INSERT INTO acon (class1_id,class2_id,onto_id) VALUES(41,42,101);<br>
     *
     *  @param class1_id    id of first class
     *  @param class2_id    id of second class
     */
    public static void insert ( java.sql.Connection conn,
                                int class1_id, int class2_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable c1 = ClassTable.getByClassID(conn, class1_id);
        ClassTable c2 = ClassTable.getByClassID(conn, class2_id);
        if(null == c1 || null == c2)
            return;
        
        AssociativeTable a = get(conn, class1_id, class2_id);
        if (a != null) 
            return;
        
        try
        {
            s = conn.createStatement ();
            str_sql = "INSERT INTO acon (class1_id,class2_id,onto_id) "+ 
                            "VALUES ("  + class1_id  + 
                                    "," + class2_id  +
                                    "," + c1.onto_id + ")";
            s.executeUpdate (str_sql);
            
        }catch(SQLException ex) {
            System.err.println("SQLException (context.AssociativeTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    /** Selects data from table 'class' if there is a pair of ontology classes 
     *  class1_id-class2_id in the table 'acon'.
     *
     *  SQL:
     *  SELECT * FROM acon WHERE class1_id=41 AND class2_id=42 AND onto_id=101;
     *
     *  @return AssociativeTable object with initialized fields: class1, class2.
     */
    public static AssociativeTable get (java.sql.Connection conn,int class1_id,int class2_id) {
                
        AssociativeTable a = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        ClassTable c1 = ClassTable.getByClassID(conn, class1_id);
        ClassTable c2 = ClassTable.getByClassID(conn, class2_id);
        if(null == c1 || null == c2)
            return null;
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT * FROM acon " +
                        "WHERE class1_id="  + class1_id + 
                         " AND class2_id="  + class2_id + 
                         " AND onto_id="    + c1.onto_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                a = new AssociativeTable ();
                a.class1 = c1;
                a.class2 = c2;
                a.id     = rs.getInt("id");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AssociativeTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }    
        return a;
    }
    
    
    /** Selects data from tables 'class' and 'attr' 
     *  if there is pair class1-class2 in the table 'acon'.
     *
     *  SQL: class1_id,class2_id,onto_id,id
     *  SELECT * FROM acon WHERE id=3;
     *  
     *  @return AssociativeTable object with initialized fields: class1, class2, id
     */
    public static AssociativeTable getByID (java.sql.Connection conn,int id) {
        
        AssociativeTable a = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT * FROM acon WHERE id=" + id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {   
                int class1_id = rs.getInt("class1_id");
                int class2_id = rs.getInt("class2_id");
                
                ClassTable c1 = ClassTable.getByClassID(conn, class1_id);
                ClassTable c2 = ClassTable.getByClassID(conn, class2_id);
                if(null == c1 || null == c2) {
                    System.out.println("Error in context.AssociativeTable.java getByID()):: class1 and/or class1 are null, id="+id+
                                        ", class1_id="+class1_id+", class2_id="+class2_id);
                } else {
                    a = new AssociativeTable ();
                    a.class1 = c1;
                    a.class2 = c2;
                    a.id = rs.getInt("id");
                }
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AssociativeTable.java getByID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s  != null) {   try { s.close();  } catch (SQLException sqlEx) { }  s  = null; }
        }    
        return a;
    }
    
    
    /** Deletes associative relation's row by class id and ontolody id.
     *  SQL:        
     *  DELETE FROM acon WHERE (class1_id=41 OR class2_id=41) AND onto_id=101;
     */
    public static void delete (java.sql.Connection conn,int class_id,int onto_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            // DELETE FROM acon WHERE (class1_id=41 OR class2_id=41) AND onto_id=101
            str_sql = "DELETE FROM acon " +
                        "WHERE (class1_id=" + class_id + 
                           " OR class2_id=" + class_id + 
                              ") AND onto_id=" + onto_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AssociativeTable.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
}
